# -*- coding = utf-8 -*-
# @Time : 2022/8/17 9:31
# @Author : 龙王赘婿_彪少
# @File : yzw3.py
# @Software: PyCharm

import openpyxl
import re


class YZW:
    def __init__(self):
        self.loadfile()
        self.printsheet()
        self.getsheet()
        # self.ergodic()
        # print("-" * 50)
        # self.ergodic2()
        # self.savefile()

    # 加载文件
    def loadfile(self):
        self.wb = openpyxl.load_workbook("../file/A-总的差异基因（GO）.xlsx")
        self.wb2 = openpyxl.load_workbook("../file/B-LIHC生存差异基因-gepia2.xlsx")
        self.wb_goal = openpyxl.Workbook()
        self.ws_goal = self.wb_goal.create_sheet("mySheet", 0)

    # 打印表单
    def printsheet(self):
        print(self.wb.sheetnames)
        print(self.wb2.sheetnames)

    # 获取表单
    def getsheet(self):
        # 方式1
        self.ws = self.wb["Sheet1"]
        self.ws2 = self.wb2["Sheet1"]
        # for i in range(1, 101):
        #     self.cell = self.ws2.cell(i, 1)
        #     print(self.cell.value)
        for k in range(1, 101):
            self.cell = self.ws2.cell(k, 1)
            print("-"*20, "finding:", self.cell.value, "-"*20)

            num = 1
            findflag = 0
            for i in self.ws.iter_rows(min_row=2, max_row=5430, min_col=1, max_col=6):
                num += 1
                # print(i[0].value, i[1].value, i[2].value, i[3].value, i[4].value, i[5].value, num)
                if(self.cell.value == i[0].value):
                    findflag = 1
                    print("-"*20, "find ", i[0].value, " in ", num, "rows", "-"*20)
                    for j in range(0, 6):
                        self.ws_goal.cell(k, j+1, value=i[j].value)
                    break
            if findflag == 0:
                print("canot find ", self.cell.value)
                self.ws_goal.cell(k, 1, self.cell.value)

        print("-"*20, "saving!", "-"*20)
        self.wb_goal.save("../file/筛选后的数据.xlsx")


            # print(re.findall(self.cell.value, i[0].value))


    def ergodic(self):
        # 列遍历 带范围
        self.nameA = []
        num = 0
        for i in self.ws.iter_cols(min_row=1, max_row=5, min_col=2, max_col=2 + 50 - 1):
            num += 1
            # print(i[0].value, i[1].value, num)
            self.nameA.append(re.findall("(.+?)-11A-", i[0].value)[0])

        # print(self.nameA)

    def savefile(self):
        self.wb.save("../file/第二套数据已处理.xlsx")

    def ergodic2(self):
        # 列遍历 带范围
        self.nameB = []
        num = 0
        for i in self.ws.iter_cols(min_row=1, max_row=5, min_col=2 + 50):
            num += 1
            # print(i[0].value, i[1].value, num)
            self.nameB.append(re.findall("(.+?)-0[1-2][A-B]-", i[0].value)[0])

        num2 = 1
        for i in self.nameA:
            num2 += 1
            if self.nameB.count(i):
                # 匹配位置 列数
                index = self.nameB.index(i) + 52
                # 复制到当前位置
                for j in range(0, 7):
                    self.ws.cell(9 + j, num2).value = self.ws.cell(1 + j, index).value


if __name__ == "__main__":
    yzw = YZW()